package com.kma.ncpractice2013.dao;

import com.kma.ncpractice2013.model.Location;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.springframework.jdbc.core.RowMapper;

/**
 *
 * @author Illya
 */
public class LocationDAO extends DAO<Location>
{

    public void create(Location location)
    {
        String sql="INSERT INTO LOCATIONS(ID,CENTROID,COUNTRY,CITY,STATE,NAME) VALUES(?,?,?,?,?,?)";
        jdbcTemplate.update(sql, new Object[]{location.getId(),location.getCentroid(),
            location.getCountry(),location.getCity(),location.getState(),location.getName()});
    }

    public void update(Location location)
    {
        String sql = "UPDATE LOCATIONS SET NAME = ?, COUNTRY = ?, CITY = ?, STATE = ? WHERE  ID = ?";
        jdbcTemplate.update(sql, new Object[]{location.getName(), location.getCountry(), location.getCity(), location.getState()});
    }

    public Location getById(int locationId)
    {
        List<Location> locationsList;;
        String sql = "SELECT * FROM LOCATIONS WHERE ID = " + locationId;
        locationsList = jdbcTemplate.query(sql, new LocationRowMapper());
        return locationsList.get(0);
    }

    public List<Location> getAll()
    {
        String sql = "SELECT * FROM LOCATIONS ";
        List<Location> userList = jdbcTemplate.query(sql, new LocationRowMapper());
        return userList;
    }

    public void deleteById(int locationId)
    {
        String sql = "DELETE FROM LOCATIONS WHERE ID = " + locationId;
        jdbcTemplate.update(sql);
    }

    private static final class LocationRowMapper implements RowMapper
    {
        public Object mapRow(ResultSet rs, int rowNum) throws SQLException
        {
            Location loc = new Location();
            loc.setId(rs.getInt("id"));
            loc.setName(rs.getString("name"));
            loc.setCentroid(rs.getString("centroid"));
            loc.setCity(rs.getString("city"));
            loc.setState("state");
            loc.setCountry(rs.getString("country"));
            return loc;
        }
    }
}
